package com.ssy.lingxi.report.dao.purchase;

import cn.hutool.core.collection.CollectionUtil;
import com.ssy.lingxi.report.entity.report.PurchaseDayReportDO;
import com.ssy.lingxi.report.model.bo.tally.statistics.PlatformPurchaseStatisticsTallyVO;
import com.ssy.lingxi.report.model.bo.tally.statistics.PurchaseInviteTenderStatisticsTallyVO;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 *  招标/投标DAO
 * @author ds
 * @date 2021/6/3
 * @version 2.0.0
 */
@Service
public class InviteTenderDAO {

    @Resource
    @Qualifier("purchaseJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 招标-统计数量
     * [待提交审核招标、待审核招标（一级）、待审核招标（二级）、待提交招标]
     * [待提交评估报告、待定标、待审核定标(一级)、待审核定标(二级)、待确认定标、待中标公示]
     * @return
     */
    public List<PurchaseInviteTenderStatisticsTallyVO> getInviteTenderStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" member_id memberId,member_role_id roleId,")
                .append(" sum(case when (invite_tender_out_status in (1,2,3) and invite_tender_in_status in (1,2,3,4,5) )then 1 else 0 end) toBeCommitValifyCount,")
                .append(" sum(case when (invite_tender_out_status =1 and invite_tender_in_status =2 )then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when (invite_tender_out_status =1 and invite_tender_in_status =2 )then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when (invite_tender_out_status =1 and invite_tender_in_status =4 )then 1 else 0 end) toBeCommitCount,")
                .append(" sum(case when (invite_tender_out_status =10 )then 1 else 0 end) toBeCommitEvaluationCount,")
                .append(" sum(case when (invite_tender_out_status =11 and invite_tender_in_status in (10,12) )then 1 else 0 end) toBeAppointCount,")
                .append(" sum(case when (invite_tender_out_status =11 and invite_tender_in_status = 11 )then 1 else 0 end) toBeAppointStep1Count,")
                .append(" sum(case when (invite_tender_out_status =11 and invite_tender_in_status = 11 )then 1 else 0 end) toBeAppointStep2Count,")
                .append(" sum(case when (invite_tender_out_status =11 and invite_tender_in_status = 13 )then 1 else 0 end) toBeConfirmAppointCount,")
                .append(" sum(case when (invite_tender_out_status =12 and invite_tender_in_status = 14 )then 1 else 0 end) toBeWinTenderPublishCount")
                .append(" FROM purchase_invite_tender ")
                .append(" group by member_id,member_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseInviteTenderStatisticsTallyVO.class));
    }

    /**
     * 招标-统计数量
     * [待审核报名、待资格预审]
     * @return
     */
    public List<PurchaseInviteTenderStatisticsTallyVO> getSubmitTenderStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" t.member_id memberId,t.member_role_id roleId,")
                .append(" sum(case when (m.submit_tender_out_status =2 )then 1 else 0 end) toBeValifySignupCount,")
                .append(" sum(case when (m.submit_tender_out_status =5 )then 1 else 0 end) toBeQualificationValifyCount")
                .append(" FROM purchase_invite_tender_member m inner join purchase_invite_tender t on m.invite_tender_id=t.id ")
                .append(" group by t.member_id,t.member_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseInviteTenderStatisticsTallyVO.class));
    }

    /**
     * 招标-统计数量
     * [待专家评标]
     * @return
     */
    public List<PurchaseInviteTenderStatisticsTallyVO> getExpertExtractStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" e.member_id memberId,e.member_role_id roleId,")
                .append(" count(1) toBeExpertEvaluationCount")
                .append(" FROM purchase_evaluation_tender_expert_extract_record eer left join purchase_evaluation_tender_expert e on eer.expert_id=e.id  ")
                .append(" group by e.member_id,e.member_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseInviteTenderStatisticsTallyVO.class));
    }

    /**
     * 招标-统计数量
     * @return
     */
    public List<PurchaseInviteTenderStatisticsTallyVO> getStatisticsCount() {
        Map<String, PurchaseInviteTenderStatisticsTallyVO> map=null;
        List<PurchaseInviteTenderStatisticsTallyVO> inviteTenderStatisticsCount = getInviteTenderStatisticsCount();
        if(CollectionUtil.isNotEmpty(inviteTenderStatisticsCount)){
            map = inviteTenderStatisticsCount.stream().collect(Collectors.toMap(o -> o.getMemberId() + ":" + o.getRoleId(), o -> o));
        }
        List<PurchaseInviteTenderStatisticsTallyVO> submitTenderStatisticsCount = getSubmitTenderStatisticsCount();
        if(CollectionUtil.isNotEmpty(submitTenderStatisticsCount)){
            if(map==null){map=new HashMap<>();}
            for(PurchaseInviteTenderStatisticsTallyVO o :submitTenderStatisticsCount){
                String key=o.getMemberId() + ":" + o.getRoleId();
                PurchaseInviteTenderStatisticsTallyVO onlineBiddingVO = map.get(key);
                if(onlineBiddingVO!=null){
                    onlineBiddingVO.setToBeValifySignupCount(onlineBiddingVO.getToBeValifySignupCount()+o.getToBeValifySignupCount());
                    onlineBiddingVO.setToBeQualificationValifyCount(onlineBiddingVO.getToBeQualificationValifyCount()+o.getToBeQualificationValifyCount());
                }else{
                    map.put(key,o);
                }
            }
        }
        List<PurchaseInviteTenderStatisticsTallyVO> expertExtractStatisticsCount = getExpertExtractStatisticsCount();
        if(CollectionUtil.isNotEmpty(expertExtractStatisticsCount)){
            if(map==null){map=new HashMap<>();}
            for(PurchaseInviteTenderStatisticsTallyVO o :expertExtractStatisticsCount){
                String key=o.getMemberId() + ":" + o.getRoleId();
                PurchaseInviteTenderStatisticsTallyVO onlineBiddingVO = map.get(key);
                if(onlineBiddingVO!=null){
                    onlineBiddingVO.setToBeExpertEvaluationCount(onlineBiddingVO.getToBeExpertEvaluationCount()+o.getToBeExpertEvaluationCount());
                }else{
                    map.put(key,o);
                }
            }
        }
        return map!=null? new ArrayList<>(map.values()) :null;
    }

    /**
     * 平台采购统计[待审核采购询价]
     * @return
     */
    public PlatformPurchaseStatisticsTallyVO getPlatformStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" count(*) toBeVerifyInviteTenderCount")
                .append(" FROM purchase_invite_tender where invite_tender_out_status=2 ;");
        return jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(PlatformPurchaseStatisticsTallyVO.class));
    }

    /**
     * 采购统计[平台审核通过的]
     * @return
     */
    public List<PurchaseDayReportDO> getPlatformExaminedStatisticsCount(Long startTime, Long endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" inq.member_id memberId,inq.member_role_id roleId,count(*) inviteTenderNum")
                .append(" FROM purchase_invite_tender inq")
                .append(" where inq.publish_time>=" + startTime)
                .append(" and inq.publish_time<=" + endTime)
                .append(" group by inq.member_id,inq.member_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseDayReportDO.class));
    }
}
